# -*- coding: utf-8 -*-
import pymssql

# 数据库连接信息
DB_CONFIG = {
    'server': '(local)',  # 替换为你的服务器地址
    'user': 'sa',  # 替换为你的用户名
    'password': '123456789',  # 替换为你的密码
    'database': 'JCDGXT',  # 替换为你的数据库名
    'charset': 'utf8'  # 指定字符集为 UTF-8
}


# 初始化数据库连接
def get_db_connection():
    return pymssql.connect(
        server=DB_CONFIG['server'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        database=DB_CONFIG['database'],
        charset=DB_CONFIG['charset']
    )


# 获取所有书商信息
def all():
    conn = get_db_connection()
    cursor = conn.cursor(as_dict=True)
    cursor.execute('SELECT bno, bname, pn FROM [businessmen$]')
    result = cursor.fetchall()
    conn.close()

    # 手动进行编码转换
    for row in result:
        row['bname'] = row['bname'].encode('latin1').decode('gbk')  # 根据实际情况调整编码方式

    return result

# 插入书商信息
def insert_businessman(bno, bname, pn):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('INSERT INTO [businessmen$] (bno, bname, pn) VALUES (%s, %s, %s)', (bno, bname, pn))
    conn.commit()
    conn.close()

def businessman_exists(bno):
    businessmen = all()
    return any(bman['bno'] == bno for bman in businessmen)

# 根据书商号查询信息
def query_by_bno(bno):
    conn = get_db_connection()
    cursor = conn.cursor(as_dict=True)
    cursor.execute('SELECT bname, pn FROM [businessmen$] WHERE bno = %s', (bno,))
    result = cursor.fetchone()
    conn.close()
    if result:
        result['bname'] = result['bname'].encode('latin1').decode('gbk')  # 根据实际情况调整编码方式
    return result


# 根据书商号更新信息
def update_by_bno(bno, data):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('UPDATE [businessmen$] SET bname=%s, pn=%s WHERE bno=%s', (data['bname'], data['pn'], bno))
    conn.commit()
    conn.close()

# 根据书商号删除信息
def delete_by_bno(bno):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('DELETE FROM [businessmen$] WHERE bno = %s', (bno,))
    conn.commit()
    conn.close()


